package system.base;

import java.sql.ResultSet;
import java.sql.SQLException;

import framework.core.*;

/*
 Author    :Wing
 CreateTime:2008-02-20 18:11:02
 */
public class GetStatistic {

    public GetStatistic() {

    }

    public static GetStatistic fetch() {
        return new GetStatistic();
    }

    // ----add by Wing start----//
    public static boolean IsCheckedOfWarehouseCode(String loginWarehouseCode, String warehouseCode) throws Exception {
        boolean checked = false;
        if (loginWarehouseCode != null) {
            if (loginWarehouseCode.equals(warehouseCode))
                return true;
        }
        return checked;
    }

    public ObjectList detailqueryGroup(DBConnection con, GetStatisticSearchEvent event) {
        ObjectList mReturn = ObjectList.getList();
        String sql = 
            "select "
                + " a.warehouse_code warehouse_code," 
                + " b.get_customer_name get_customer_name," 
                + " sum(b.product_money) total_money"
            +" from "
                +"t_out_head a,t_out_sub b,t_product c" 
                + " where b.status = 1 and a.id = b.out_bill_id and b.product_id = c.id";
        if (event.getWarehouseCodes() != null) {
            sql += " and a.warehouse_code in (";
            for (int i = 0; i < event.getWarehouseCodes().length; i++) {
                if (i > 0)
                    sql += ",";
                sql += "'" + event.getWarehouseCodes()[i] + "'";
            }
            sql += ")";
        }

        if (!event.getGetCustomerCode().equalsIgnoreCase("")) {
            try {
                ObjectList custList = (ObjectList) Customer.fetchAllOrgs(con, event.getGetCustomerCode());
                sql += " and b.get_customer_code in (";
                for (int i = 0; i < custList.size(); i++) {
                    Customer customer = (Customer) custList.get(i);
                    if (i > 0)
                        sql += ",";
                    sql += "'" + customer.getCode() + "'";
                }
                sql += ")";
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (!event.getOutType().equalsIgnoreCase("")) {
            sql += " and a.out_type = '" + event.getOutType() + "'";
        }
        if (!event.getBalanceFlag().equalsIgnoreCase("")) {
            sql += " and a.balance_flag = '" + event.getBalanceFlag() + "'";
        }
        if (!event.getStartOutTime().equalsIgnoreCase("")) {
            sql += " and a.out_time >= '" + event.getStartOutTime() + " 00:00:00'";
        }
        if (!event.getEndOutTime().equalsIgnoreCase("")) {
            sql += " and a.out_time <= '" + event.getEndOutTime() + " 23:59:59'";
        }
        if (!event.getIsNormal().equalsIgnoreCase("")) {
            sql += " and c.is_normal = '" + event.getIsNormal() + "'";
        }
        if (!event.getBalanceMode().equalsIgnoreCase("")) {
            sql += " and b.balance_mode = '" + event.getBalanceMode() + "'";
        }

        if (event.getWarehouseCodeUser().length() > 0) {
            sql += " and a.WAREHOUSE_CODE like '" + event.getWarehouseCodeUser() + "%'";
        }

        sql += " group by a.warehouse_code,b.get_customer_name";
        try {
            UniversalTable ut = new UniversalTable();
            ut.initial(con, sql);
            mReturn = ut.getList();
            // 郑斌
            String result = getMoneyCount("total_money", con, sql);
            mReturn.add(result);
            // 郑斌
        } catch (Exception e) {
            System.out.println("GetStatistic SQL detailqueryGroup error:" + e.getMessage());
        }
        /*
         * finally{ try{ if(con!=null) con.close(); }catch(Exception e1){
         *  } }
         */
        mReturn.size();
        return mReturn;
    }
    
    public ObjectList detailqueryGroupForHour(DBConnection con, GetStatisticSearchEvent event) {
        ObjectList mReturn = ObjectList.getList();
        String sql = 
            "select "
                + " a.warehouse_code warehouse_code,"           
                + " b.get_customer_code,"
                + " b.get_customer_name get_customer_name," 
                + " sum(b.product_money) total_money"
            +" from "
                +"t_out_head a,t_out_sub b,t_product c" 
                + " where b.status = 1 and a.id = b.out_bill_id and b.product_id = c.id";
        if (event.getWarehouseCodes() != null) {
            sql += " and a.warehouse_code in (";
            for (int i = 0; i < event.getWarehouseCodes().length; i++) {
                if (i > 0)
                    sql += ",";
                sql += "'" + event.getWarehouseCodes()[i] + "'";
            }
            sql += ")";
        }

        if (!event.getGetCustomerCode().equalsIgnoreCase("")) {
            try {
                ObjectList custList = (ObjectList) Customer.fetchAllOrgs(con, event.getGetCustomerCode());
                sql += " and b.get_customer_code in (";
                for (int i = 0; i < custList.size(); i++) {
                    Customer customer = (Customer) custList.get(i);
                    if (i > 0)
                        sql += ",";
                    sql += "'" + customer.getCode() + "'";
                }
                sql += ")";
            } catch (Exception e) {
                e.printStackTrace();
            }
        }

        if (!event.getOutType().equalsIgnoreCase("")) {
            sql += " and a.out_type = '" + event.getOutType() + "'";
        }
        if (!event.getBalanceFlag().equalsIgnoreCase("")) {
            sql += " and a.balance_flag = '" + event.getBalanceFlag() + "'";
        }
        if (!event.getStartOutTime().equalsIgnoreCase("")) {
            sql += " and a.out_time >= '" + event.getStartOutTime()+"'";
        }
        if (!event.getEndOutTime().equalsIgnoreCase("")) {
            sql += " and a.out_time <= '" + event.getEndOutTime()+"'";
        }
        if (!event.getIsNormal().equalsIgnoreCase("")) {
            sql += " and c.is_normal = '" + event.getIsNormal() + "'";
        }
        if (!event.getBalanceMode().equalsIgnoreCase("")) {
            sql += " and b.balance_mode = '" + event.getBalanceMode() + "'";
        }

        if (event.getWarehouseCodeUser().length() > 0) {
            sql += " and a.WAREHOUSE_CODE like '" + event.getWarehouseCodeUser() + "%'";
        }
        
        if(!"".equals(event.getPositionCode())){
        	 sql += " and b.position_code = '" + event.getPositionCode() + "'";
        }

        sql += " group by a.warehouse_code,b.get_customer_name"+
        		",b.get_customer_code";
        try {
            UniversalTable ut = new UniversalTable();
            ut.initial(con, sql);
            mReturn = ut.getList();
            // 郑斌
            String result = getMoneyCount("total_money", con, sql);
            mReturn.add(result);
            // 郑斌
        } catch (Exception e) {
            System.out.println("GetStatistic SQL detailqueryGroup error:" + e.getMessage());
        }
        /*
         * finally{ try{ if(con!=null) con.close(); }catch(Exception e1){
         *  } }
         */
        mReturn.size();
        return mReturn;
    }

    // 指定字段获得sum
    public static String getMoneyCount(String field, DBConnection con, String sql) {
        String result = "";
        String sql2 = "select sum(" + field + ") from (" + sql + ")";
        DBResult dbrs = null;
        ResultSet rs = null;
        try {
            dbrs = DBEngine.doSelect(con, sql2);
            rs = dbrs.getRs();
            if (rs.next()) {
                result = rs.getString(1);
            }
        } catch (SQLException e) {
            
            e.printStackTrace();
        } catch (Exception e) {
            
            e.printStackTrace();
        } finally {
            try {
                dbrs.clear();
                return result;
            } catch (Exception e) {
                
                e.printStackTrace();
            }
        }
        return result;
    }

    //----add by Wing end----//

}
